
--select * from fn_get_process_flow_by_order ( 201 )
ALTER FUNCTION fn_get_process_flow_by_order (@baseProduct_Id INT)
RETURNS @fn_result Table(ProcessStep_ID INT, 
 			Process_Type_Name VARCHAR(50), PS_Type_Id INT, Location_Id INT )
  
as
BEGIN
 	DECLARE @result TABLE( ProcessStep_ID INT, 
 			Process_Type_Name VARCHAR(50), PS_Type_Id INT, Location_Id INT, ORDERID INT  )
	DECLARE @FAB_PROCESSSTEP_ID INT
	DECLARE @PS_TYPE_NAME VARCHAR(50)
	DECLARE @PREV_PS_TYPE_NAME VARCHAR(50)
	DECLARE @PROCESSSTEP_ID INT
	DECLARE @TEMP_PROCESSSTEP_ID INT
	DECLARE @ORDERID INT
	DECLARE @recordsPresent INT

	IF( ( SELECT ISNULL( P_TYPE, 'CHIP' ) FROM PRODUCT WHERE PRODUCT_ID = @baseProduct_Id ) = 'CHIP' )
	BEGIN
		INSERT INTO @result SELECT P.PROCESSSTEP_ID, PS_TYPE_NAME, 1, LOCATION_ID, 0 FROM PRODUCT P, PROCESS_STEP PS, PROCESS_Step_Location_Table PSLT WHERE
				P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND P.ProcessStep_id = PSLT.ProcessStep_Id AND PS_TYPE_NAME = 'Fab' AND P_Parent_Product_Id = @baseProduct_Id
	
		SELECT @recordsPresent = COUNT(*) FROM @result WHERE ORDERID = 0
		SET @ORDERID = 1
		WHILE @recordsPresent > 0
		BEGIN
			SET @ORDERID = @ORDERID + 1
			SELECT TOP 1 @PROCESSSTEP_ID = PROCESSSTEP_ID FROM @result WHERE ORDERID = 0
			SELECT @recordsPresent = COUNT(*) FROM Process_Step_Traverse_Table WHERE ProcessStep_ID = @PROCESSSTEP_ID
			IF @recordsPresent > 0
			BEGIN
				--SELECT @TEMP_PROCESSSTEP_ID = Next_ProcessStep_ID FROM Process_Step_Traverse_Table WHERE ProcessStep_Id = @PROCESSSTEP_ID
				DELETE FROM @result WHERE ProcessStep_Id IN ( SELECT Next_ProcessStep_ID FROM Process_Step_Traverse_Table WHERE ProcessStep_Id = @PROCESSSTEP_ID )
				INSERT INTO @result SELECT PSTT.Next_ProcessStep_ID, PS_Type_Name, CASE 
					WHEN PS_TYPE_NAME='FAB' 	THEN 1
					WHEN PS_TYPE_NAME='WAFER STORE' THEN 2
					WHEN PS_TYPE_NAME='SORT' 	THEN 3
					WHEN PS_TYPE_NAME='DIE BANK' 	THEN 4
					WHEN PS_TYPE_NAME='ASSEMBLY' 	THEN 5
					WHEN PS_TYPE_NAME='TEST' 	THEN 6
					WHEN PS_TYPE_NAME='CLASS STORE' THEN 7
					WHEN PS_TYPE_NAME='MARK & PACK' THEN 8
					WHEN PS_TYPE_NAME='FINISHED GOODS' THEN 9 END AS PS_TYPE_ID, PSLT.Location_Id, -1
					FROM Process_Step PS, Process_Step_Traverse_Table PSTT, Process_Step_Location_Table PSLT
					WHERE PS.ProcessStep_Id = PSTT.Next_ProcessStep_ID AND PSTT.Next_ProcessStep_ID = PSLT.ProcessStep_Id
					AND PSTT.ProcessStep_Id = @PROCESSSTEP_ID
				UPDATE @result SET ORDERID = @ORDERID WHERE ProcessStep_ID = @PROCESSSTEP_ID
				
				SELECT @recordsPresent = COUNT(*) FROM @result WHERE ORDERID = 0
				IF @recordsPresent = 0
				BEGIN
					UPDATE @result SET ORDERID = 0 WHERE ORDERID = -1
				END
			END
			ELSE
			BEGIN
				UPDATE @result SET ORDERID = @ORDERID WHERE ProcessStep_ID = @PROCESSSTEP_ID
			END
			SELECT @recordsPresent = COUNT(*) FROM @result WHERE ORDERID = 0
		END
 	END
 	ELSE
 	BEGIN
		--ONLY TO LOAD BOARD BASE PRODUCT
 		INSERT INTO @result 
 		SELECT P.PROCESSSTEP_ID, PS_TYPE_NAME,
 		CASE 
 			WHEN PS_TYPE_NAME='FAB' 	THEN 1
 			WHEN PS_TYPE_NAME='WAFER STORE' THEN 2
 			WHEN PS_TYPE_NAME='SORT' 	THEN 3
 			WHEN PS_TYPE_NAME='DIE BANK' 	THEN 4
 			WHEN PS_TYPE_NAME='ASSEMBLY' 	THEN 5
 			WHEN PS_TYPE_NAME='TEST' 	THEN 6
 			WHEN PS_TYPE_NAME='CLASS STORE' THEN 7
 			WHEN PS_TYPE_NAME='MARK & PACK' THEN 8
 			WHEN PS_TYPE_NAME='FINISHED GOODS' THEN 9
 		END AS PS_TYPE_ID, PSLT.Location_Id, 0
 		FROM PRODUCT P, PROCESS_STEP PS, PROCESS_STEP_LOCATION_TABLE PSLT
 		WHERE P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND P.PROCESSSTEP_ID = PSLT.ProcessStep_ID AND ( P.PRODUCT_ID = @baseProduct_Id )
 	END
		
	INSERT INTO @fn_result SELECT ProcessStep_ID, Process_Type_Name, PS_Type_Id, Location_Id FROM @result
	RETURN
 	 
END
